package org.languageguide.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.languageguide.storage.DBConnector;




public class SavedWordManager {

/*	final String loadSql = "SELECT * FROM SavedWord " +
			"LEFT JOIN ToTranslate Where SavedWords.ToTranslateId = ToTranslate.Id " +
			"Left JOIN Translated On ToTranslate.Id = Translated.ToTranslateId AND Translated.TargetLanguage = SavedWords.TargetLanguage " + 
			"WHERE UserId = ? AND FolderId = ? ORDER BY DATE";*/
	final String loadSqlFolder = "SELECT * FROM SavedWords " +
			"LEFT JOIN ToTranslate ON SavedWords.ToTranslateId = ToTranslate.Id " +
			"Left JOIN Translated ON ToTranslate.Id = Translated.ToTranslateId AND (Translated.TargetLanguage = SavedWords.TargetLanguage OR Translated.TargetLanguage= SavedWords.Language) " + 
			"Left JOIN Translated AS RealTrans ON SavedWords.TranslationToTranslateId = RealTrans.ToTranslateId AND (RealTrans.TargetLanguage = SavedWords.TargetLanguage) " +
			"WHERE SavedWords.UserId = ? AND FolderId = ? ORDER BY SavedWords.DATE DESC";

	final String loadSqlMainFolder = "SELECT * FROM SavedWords " +
			"LEFT JOIN ToTranslate ON SavedWords.ToTranslateId = ToTranslate.Id " +
			"Left JOIN Translated ON ToTranslate.Id = Translated.ToTranslateId AND (Translated.TargetLanguage = SavedWords.TargetLanguage OR Translated.TargetLanguage= SavedWords.Language) " +
			"Left JOIN Translated AS RealTrans ON SavedWords.TranslationToTranslateId = RealTrans.ToTranslateId AND (RealTrans.TargetLanguage = SavedWords.TargetLanguage) " +					
			"WHERE SavedWords.UserId = ? AND FolderId IS NULL ORDER BY SavedWords.DATE DESC";
	static SavedWordManager me;

	static public SavedWordManager getInstance() {
		return me;
	}

	static {
		me = new SavedWordManager();

	}

	/*public List<SavedWord> getSavedWords(User user, String folderIdStr){
		List<SavedWord> list = new ArrayList<SavedWord>();
		Integer folderId = Integer.parseInt(folderIdStr);
		//String hsUserSql = "SELECT * FROM SavedWord WHERE UserId = ? AND FolderId = ? ORDER BY DATE";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement querySavedWords = connection.prepareStatement(loadSql);
			querySavedWords.setInt(1, user.getId());
			//if (folderId == null){
			//querySavedWords.setNull(2, folderId);	
			//}
			//else{
			querySavedWords.setInt(2, folderId);
			//}
			ResultSet rs = querySavedWords.executeQuery();
			while (rs.next()){
				SavedWord word = new SavedWord(rs);
				list.add(word);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		System.out.println("coun't find user");

		return null;
	}
*/
	
	
	public List<SavedWord> getSavedWords(User user, String folderIdStr){
		List<SavedWord> list = new ArrayList<SavedWord>();
		//Integer folderId = Integer.parseInt(folderIdStr);
		//String hsUserSql = "SELECT * FROM SavedWord WHERE UserId = ? AND FolderId IS NULL ORDER BY DATE";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement querySavedWords = connection.prepareStatement(loadSqlFolder);
			querySavedWords.setInt(1, user.getId());
			querySavedWords.setInt(2, Integer.parseInt(folderIdStr));
			ResultSet rs = querySavedWords.executeQuery();
			boolean hasMore = rs.next();
			while (hasMore){
				SavedWord word = new SavedWord(rs);	
				list.add(word);
				hasMore = word.hasMore();
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		System.out.println("coun't find user");
		return null;
	}
	

	public List<SavedWord> getSavedWords(User user){
		List<SavedWord> list = new ArrayList<SavedWord>();
		//Integer folderId = Integer.parseInt(folderIdStr);
		//String hsUserSql = "SELECT * FROM SavedWord WHERE UserId = ? AND FolderId IS NULL ORDER BY DATE";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement querySavedWords = connection.prepareStatement(loadSqlMainFolder);
			querySavedWords.setInt(1, user.getId());
			ResultSet rs = querySavedWords.executeQuery();
			boolean hasMore = rs.next();
			while (hasMore){
				SavedWord word = new SavedWord(rs);	
				list.add(word);
				hasMore = word.hasMore();
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		System.out.println("coun't find user");
		return null;
	}

	public List<SavedWord> getSavedWords(){
		List<SavedWord> list = new ArrayList<SavedWord>();
		//Integer folderId = Integer.parseInt(folderIdStr);
		//String hsUserSql = "SELECT * FROM SavedWord WHERE UserId = ? AND FolderId IS NULL ORDER BY DATE";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement querySavedWords = connection.prepareStatement(loadSqlMainFolder);
			querySavedWords.setInt(1, 460);
			ResultSet rs = querySavedWords.executeQuery();
			boolean hasMore = rs.next();
			while (hasMore){
				SavedWord word = new SavedWord(rs);	
				list.add(word);
				hasMore = word.hasMore();
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		System.out.println("coun't find user");
		return null;
	}


	public void saveWord(String id, String translationToTranslateId, String language, String target, User user){
		String sql = "INSERT INTO SavedWords (ToTranslateId, TranslationToTranslateId, Language, TargetLanguage, UserId, Created) VALUES (?, ?, ?, ?, ?, NOW());";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement saveWords = connection.prepareStatement(sql);
			saveWords.setString(1, id);
			saveWords.setString(2, translationToTranslateId);
			saveWords.setString(3, language);
			saveWords.setString(4, target);
			saveWords.setInt(5, user.getIdInteger());

			saveWords.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
	}

	public String getIdString(String[] ids){
		String idList = ids[0];
		for (int i = 1; i <ids.length; i++){
			idList += "," + ids[i];
		}
		return idList;
	}


	public Integer createFolder(String name, User user){
		String sql = "Insert INTO SavedWordFolder (UserId, Name) VALUES (?, ?);";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement querySavedWords = connection.prepareStatement(sql,
                    Statement.RETURN_GENERATED_KEYS);
			querySavedWords.setInt(1, user.getId());
			querySavedWords.setString(2, name);
			querySavedWords.executeUpdate();
			ResultSet rs =querySavedWords.getGeneratedKeys();
			if (rs.next()) {
				Integer key = rs.getInt(1);
				return key;
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}	
		return null;

	}

	public Folder getDeletedFolderIfUserHasDeletedWords(User user){
		String sql = "SELECT * FROM SavedWords WHERE UserId = ? AND FolderId = 1";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement getFolders = connection.prepareStatement(sql);
			getFolders.setInt(1, user.getId());
			ResultSet rs = getFolders.executeQuery();
			if (rs.next()){
				return new Folder("Deleted Words", 1); 
			}
			return null;
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}
	
	public List<Folder> getFolders(User user){
		List<Folder> list = new ArrayList<Folder>();
		String sql = "SELECT * FROM SavedWordFolder WHERE UserId = ? ";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement getFolders = connection.prepareStatement(sql);
			getFolders.setInt(1, user.getId());
			ResultSet rs = getFolders.executeQuery();
			while (rs.next()){
				Folder folder = new Folder(rs);
				list.add(folder);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return list;
	}
	
	public class Folder{
		String name;
		Integer id;
		
		public Folder(ResultSet rs) throws SQLException{
			name = rs.getString("Name");
			id = rs.getInt("Id");
		}
		
		public Folder(String name, Integer id){
			this.name = name;
			this.id = id;
		}
		
		public String getSelectionOption(String currentFolderIdStr){
			Integer currentFolderId;
			if (currentFolderIdStr == null){
				currentFolderId = 0;
			}
			else{
				currentFolderId = Integer.parseInt(currentFolderIdStr);
			}
			if (currentFolderId == id)
				return "";
			else{
				return "<option value=\"" + id +"\">" +name + "</option>";
			}
		}
		
		public String getDisplay(String currentFolderIdStr){
			Integer currentFolderId;
			boolean folderSelected;
			if (currentFolderIdStr == null){
				currentFolderId = 0;
			}
			else{
				currentFolderId = Integer.parseInt(currentFolderIdStr);
			}
			if (id == currentFolderId){
				folderSelected = true;
			}
			else folderSelected = false;
			
			if (!folderSelected){
				if (id != 0){
					return "<li id=\"f" +id + "\"><a href=\"/user/account.jsp?folderId=" + id + "\">" + name + "</a></li>";
				}
				else{
					return "<li><a href=\"/user/account.jsp\">" + name + "</a></li>";
				}
			}
			else{
				return "<li><strong>" + name + "</strong></li>";
			}
		}

	}
	
	public Folder getMainFolder(){
		return new Folder("Main", 0);
	}

	String idString;
	public void changeFolder(String[] ids, Integer folderId, User user){
		String idString = getIdString(ids);
		String sql = "Update SavedWords Set FolderId = " + folderId +" WHERE SavedWords.UserId = " +user.getId() + " AND Id IN (" + idString + ")";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			Statement changeFolderOfWords = connection.createStatement();
			System.out.println(sql);
			changeFolderOfWords.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}	

	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		SavedWordManager manager = new SavedWordManager();
		manager.getSavedWords();
	}

}
